package tech.mhuang.interchan.generator.manager.mysql;

import lombok.extern.slf4j.Slf4j;
import tech.mhuang.core.date.DateTimeUtil;
import tech.mhuang.core.util.StringUtil;
import tech.mhuang.ext.interchan.protocol.data.PageDTO;
import tech.mhuang.interchan.generator.dto.ColumnDTO;
import tech.mhuang.interchan.generator.dto.GeneratorColumnDTO;
import tech.mhuang.interchan.generator.dto.GeneratorTableDTO;
import tech.mhuang.interchan.generator.dto.TableDTO;
import tech.mhuang.interchan.generator.entity.GbDataBase;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.stream.Collectors;

@Slf4j
public class MysqlManager {

    final static String driver = "com.mysql.cj.jdbc.Driver";

    private static String getUrl(GbDataBase dataBase) {
        StringBuilder urlBuilder = new StringBuilder().append("jdbc:mysql://")
                .append(dataBase.getIp()).append(":").append(dataBase.getPort())
                .append("/").append(dataBase.getDbName());
        if (StringUtil.isNotEmpty(dataBase.getExtra())) {
            urlBuilder.append("?").append(dataBase.getExtra());
        }
        return urlBuilder.toString();
    }

    public static boolean validate(GbDataBase dataBase) throws SQLException {
        boolean rst = false;
        Connection con = null;
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(
                    getUrl(dataBase),
                    dataBase.getUsername(),
                    dataBase.getPassword());
            if (!con.isClosed()) {
                rst = true;
            }
        } catch (ClassNotFoundException e) {
            log.error("找不到mysql驱动,请先引入所需的mysql包", e);
        } catch (SQLException e) {
            log.error("sql执行异常", e);
        } finally {
            if (con != null) {
                if (!con.isClosed()) {
                    con.close();
                }
            }
        }

        return rst;
    }

    public static int countTable(GbDataBase dataBase,String tableName) throws SQLException {
        int count = 0;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(
                    getUrl(dataBase),
                    dataBase.getUsername(),
                    dataBase.getPassword());
            StringBuilder querySql = new StringBuilder("select count(1) from information_schema.tables ");
            querySql.append("where table_schema = ? ");
            if(StringUtil.isNotEmpty(tableName)){
                querySql.append("and table_name like ?");
            }
            ps = con.prepareStatement(querySql.toString());
            ps.setObject(1, dataBase.getDbName());
            if(StringUtil.isNotEmpty(tableName)){
                ps.setObject(2,"%"+tableName+"%");
            }
            rs = ps.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (ClassNotFoundException e) {
            log.error("找不到mysql驱动,请先引入所需的mysql包", e);
        } catch (SQLException e) {
            log.error("sql执行异常", e);
        } finally {
            if (con != null) {
                if (!con.isClosed()) {
                    con.close();
                }
            }
            if (ps != null) {
                if (ps.isClosed()) {
                    ps.close();
                }
            }
            if (rs != null) {
                if (rs.isClosed()) {
                    rs.close();
                }
            }
        }
        return count;
    }

    public static List<TableDTO> pageTable(GbDataBase dataBase,String tableName, PageDTO pageDTO) throws SQLException {
        List<TableDTO> rst = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        TableDTO tableDTO = null;
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(
                    getUrl(dataBase),
                    dataBase.getUsername(),
                    dataBase.getPassword());
            StringBuilder querySql = new StringBuilder("select table_name , engine,table_collation , table_comment , create_time  from information_schema.tables ");
            querySql.append("where table_schema = ? ");
            if(StringUtil.isNotEmpty(tableName)){
                querySql.append("and table_name like ? ");
            }
            querySql.append("limit ?,?");
            ps = con.prepareStatement(querySql.toString());
            ps.setObject(1, dataBase.getDbName());
            if(StringUtil.isNotEmpty(tableName)){
                ps.setObject(2,"%"+tableName+"%");
                ps.setObject(3, (pageDTO.getStart() - 1) * pageDTO.getRows());
                ps.setObject(4, pageDTO.getRows());
            }else{
                ps.setObject(2, (pageDTO.getStart() - 1) * pageDTO.getRows());
                ps.setObject(3, pageDTO.getRows());
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                tableDTO = new TableDTO();
                tableDTO.setTableName(rs.getString(1));
                tableDTO.setEngine(rs.getString(2));
                tableDTO.setTableCollation(rs.getString(3));
                tableDTO.setTableComment(rs.getString(4));
                tableDTO.setCreateTime(rs.getDate(5));
                rst.add(tableDTO);
            }
        } catch (ClassNotFoundException e) {
            log.error("找不到mysql驱动,请先引入所需的mysql包", e);
        } catch (SQLException e) {
            log.error("sql执行异常", e);
        } finally {
            if (con != null) {
                if (!con.isClosed()) {
                    con.close();
                }
            }
            if (ps != null) {
                if (ps.isClosed()) {
                    ps.close();
                }
            }
            if (rs != null) {
                if (rs.isClosed()) {
                    rs.close();
                }
            }
        }
        return rst;
    }

    public static List<ColumnDTO> queryColumnsById(GbDataBase dataBase, String tableName) throws SQLException {
        List<ColumnDTO> rst = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ColumnDTO columnDTO = null;
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(
                    getUrl(dataBase),
                    dataBase.getUsername(),
                    dataBase.getPassword());
            StringBuilder querySql = new StringBuilder("select column_name,column_type,column_comment,column_key,is_nullable,collation_name,extra from information_schema.columns ");
            querySql.append("where table_name = ?  and table_schema = ?");
            ps = con.prepareStatement(querySql.toString());
            ps.setObject(1, tableName);
            ps.setObject(2, dataBase.getDbName());
            rs = ps.executeQuery();
            while (rs.next()) {
                columnDTO = new ColumnDTO();
                columnDTO.setColumnName(rs.getString(1));
                columnDTO.setColumnType(rs.getString(2));
                columnDTO.setColumnComment(rs.getString(3));
                columnDTO.setColumnKey(rs.getString(4));
                columnDTO.setIsNullable(rs.getString(5));
                columnDTO.setCollationName(rs.getString(6));
                columnDTO.setExtra(rs.getString(7));
                rst.add(columnDTO);
            }
        } catch (ClassNotFoundException e) {
            log.error("找不到mysql驱动,请先引入所需的mysql包", e);
        } catch (SQLException e) {
            log.error("sql执行异常", e);
        } finally {
            if (con != null) {
                if (!con.isClosed()) {
                    con.close();
                }
            }
            if (ps != null) {
                if (ps.isClosed()) {
                    ps.close();
                }
            }
            if (rs != null) {
                if (rs.isClosed()) {
                    rs.close();
                }
            }
        }
        return rst;
    }

    public static List<GeneratorTableDTO> queryColumnsByTableNames(GbDataBase dataBase,String packages, String tables) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Map<String, GeneratorTableDTO> map = new ConcurrentHashMap<>();
        try {
            Class.forName(driver);
            con = DriverManager.getConnection(
                    getUrl(dataBase),
                    dataBase.getUsername(),
                    dataBase.getPassword());
            StringBuilder querySql = new StringBuilder("SELECT t.TABLE_NAME,t.table_comment,c.column_name,c.data_type,if(c.COLUMN_KEY = 'PRI',TRUE,FALSE),c.column_comment from information_schema.columns c ");
            querySql.append("INNER JOIN information_schema.TABLES t ON c.TABLE_NAME = t.TABLE_NAME  and c.TABLE_SCHEMA = t.TABLE_SCHEMA ");
            querySql.append("where t.table_schema = ? ");
            if(StringUtil.isNotEmpty(tables)){
                String[] tableArr = StringUtil.split(tables,",");
                if(tableArr.length == 1){
                    querySql.append("and t.table_name = '").append(tableArr[0]).append("' ");
                }else{
                    querySql.append("and ( ");
                    querySql.append("t.table_name = '").append(tableArr[0]).append("' ");
                    for(int i=1;i < tableArr.length ;i++){
                        querySql.append("or ");
                        querySql.append("t.table_name = '").append(tableArr[i]).append("' ");
                    }
                    querySql.append(")");
                }
            }
            ps = con.prepareStatement(querySql.toString());
            ps.setObject(1, dataBase.getDbName());
            rs = ps.executeQuery();

            String tableName = null;
            String currentDate = DateTimeUtil.fromDateTime(LocalDateTime.now());
            GeneratorColumnDTO columnDTO = null;
            while (rs.next()) {
                tableName = rs.getString(1);

                //表
                GeneratorTableDTO tableDTO = map.getOrDefault(tableName,new GeneratorTableDTO());
                tableDTO.setSourceName(tableName);
                tableDTO.setPackages(packages);
                tableDTO.setDate(currentDate);
                tableDTO.setAuthor("mhuang");
                tableDTO.setDescription(rs.getString(2));

                //列
                columnDTO = new GeneratorColumnDTO();
                columnDTO.setSourceName(rs.getString(3));
                columnDTO.setSourceType(rs.getString(4));
                columnDTO.setType(MysqlConsts.FIELD_JAVA_TYPE.get(columnDTO.getSourceType(),String.class));
                columnDTO.setMybatisType((String) MysqlConsts.FIELD_MYBATIS_TYPE.getOrDefault(columnDTO.getSourceType(),columnDTO.getSourceType()));
                columnDTO.setPk(rs.getBoolean(5));
                columnDTO.setComments(rs.getString(6));
                columnDTO.setName(camelCaseName(columnDTO.getSourceName().toLowerCase(),false));
                tableDTO.getColumns().add(columnDTO);

                if("bigDecimal".equals(columnDTO.getType())){
                    tableDTO.setHasBigDecimal(true);
                }
                if("Date".equals(columnDTO.getType())){
                    tableDTO.setHasDate(true);
                }
                tableDTO.setEntityClassName(camelCaseName(tableDTO.getSourceName().toLowerCase(),true));
                map.put(tableName,tableDTO);
            }
        } catch (ClassNotFoundException e) {
            log.error("找不到mysql驱动,请先引入所需的mysql包", e);
        } catch (SQLException e) {
            log.error("sql执行异常", e);
        } finally {
            if (con != null) {
                if (!con.isClosed()) {
                    con.close();
                }
            }
            if (ps != null) {
                if (ps.isClosed()) {
                    ps.close();
                }
            }
            if (rs != null) {
                if (rs.isClosed()) {
                    rs.close();
                }
            }
        }
        return map.values().stream().collect(Collectors.toList());
    }

    public static String camelCaseName(String underscoreName,boolean firstUpper) {
        //确保扩容只会出现一次
        StringBuilder result = new StringBuilder(underscoreName.length());
        if(firstUpper){
            result.append(underscoreName.substring(0,1).toUpperCase());
        }else{
            result.append(underscoreName.charAt(0));
        }
        boolean flag = false;
        for (int i = 1; i < underscoreName.length(); i++) {
            char ch = underscoreName.charAt(i);
            if ("_".charAt(0) == ch) {
                flag = true;
            } else {
                if (flag) {
                    result.append(Character.toUpperCase(ch));
                    flag = false;
                } else {
                    result.append(ch);
                }
            }
        }
        return result.toString();

    }
}
